package org.light.wizard;

import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.light.core.LayoutComb;
import org.light.core.ReportComb;
import org.light.domain.Domain;
import org.light.domain.Dropdown;
import org.light.domain.Field;
import org.light.domain.ManyToMany;
import org.light.domain.ManyToManyCandidate;
import org.light.domain.Pair;
import org.light.domain.Project;
import org.light.layouts.ParentChildGridLayout;
import org.light.layouts.TreeGridLayout;
import org.light.layouts.TreeParentChildLayout;
import org.light.reports.EChartsCompareGridReport;
import org.light.reports.EChartsGridReport;
import org.light.reports.EChartsReport;
import org.light.shiroauth.ShiroAuthModule;
import org.light.utils.DomainUtil;
import org.light.utils.StringUtil;

public class ExcelWizard {
	public static void outputExcelWorkBook(Project project, String outputFolder,String fileName) throws Exception{
		File file = new File(outputFolder+"/"+ fileName);
		try(OutputStream out = new FileOutputStream(file)){
			List<Pair> contents = new ArrayList<Pair>();
			Pair projectName = new Pair("project",project.getStandardName());
			Pair packagetoken = new Pair("packagetoken",project.getPackageToken());
			Pair dbprefix = new Pair("dbprefix",project.getDbPrefix());
			Pair dbname = new Pair("dbname",project.getDbName());	
			Pair dbusername = new Pair("dbusername",project.getDbUsername());
			Pair dbpassword = new Pair("dbpassword",project.getDbPassword());
			String dbTypeStr = "MariaDB";
			if ("datadummy".equalsIgnoreCase(project.getSchema())) {
				dbTypeStr = "MariaDB";
			} else {
				dbTypeStr = project.getDbType();
			}
			Pair dbtype = new Pair("dbtype",dbTypeStr);
			Pair technicalstack = new Pair("technicalstack",project.getTechnicalstack());
			Pair title = new Pair("title",project.getTitle());
			Pair subtitle = new Pair("subtitle",project.getSubTitle());
			Pair footer = new Pair("footer",project.getFooter());
			Pair crossorigin = new Pair("crossorigin",project.getCrossOrigin());
			Pair resolution = new Pair("resolution",project.getResolution());
			Pair domainsuffix = new Pair("domainsuffix",project.getDomainSuffix());
			Pair daosuffix = new Pair("daosuffix",project.getDaoSuffix());
			Pair daoimplsuffix = new Pair("daoimplsuffix",project.getDaoimplSuffix());
			Pair servicesuffix = new Pair("servicesuffix",project.getServiceSuffix());
			Pair serviceimplsuffix = new Pair("serviceimplsuffix",project.getServiceimplSuffix());
			Pair controllersuffix = new Pair("controllersuffix",project.getControllerSuffix());
			Pair domainnamingsuffix = new Pair("domainnamingsuffix",project.getDomainNamingSuffix());
			Pair controllernamingsuffix = new Pair("controllernamingsuffix",project.getControllerNamingSuffix());
			Pair language = new Pair("language",project.getLanguage());
			Pair schema = new Pair("schema",project.getSchema());			Pair frontbaseapi = new Pair("frontbaseapi",project.getFrontBaseApi());
			Pair frontendUi = new Pair("frontendUi",project.getFrontendUi());
			Pair backendUi = new Pair("backendUi",project.getBackendUi());
			Pair computerlanguage = new Pair("computerlanguage",project.getComputerLanguage());
			
			contents.add(projectName);
			contents.add(packagetoken);
			contents.add(dbprefix);
			contents.add(dbname);
			contents.add(dbusername);
			contents.add(dbpassword);
			contents.add(dbtype);
			contents.add(technicalstack);
			contents.add(title);
			contents.add(subtitle);
			contents.add(footer);
			contents.add(crossorigin);
			contents.add(resolution);
			contents.add(domainsuffix);
			contents.add(daosuffix);
			contents.add(daoimplsuffix);
			contents.add(servicesuffix);
			contents.add(serviceimplsuffix);
			contents.add(controllersuffix);
			contents.add(domainnamingsuffix);
			contents.add(controllernamingsuffix);
			contents.add(language);
			contents.add(schema);
			contents.add(frontbaseapi);
			contents.add(frontendUi);
			contents.add(backendUi);
			contents.add(computerlanguage);
			
			HSSFWorkbook wb = new HSSFWorkbook();
			exportProjectExcelSheet(wb,"Project", contents);
			List<Domain> domains = project.getDomains();
			List<List<Domain>> datadomains = project.getDataDomains();
			for (int i=0;i<domains.size();i++) {
				if(domains.get(i) instanceof org.light.domain.Enum) {
					exportDomainExcelSheet(wb,"Enum"+(i+1),domains.get(i),datadomains);
				}else {
					exportDomainExcelSheet(wb,"Domain"+(i+1),domains.get(i),datadomains);
				}
			}	

			List<ManyToManyCandidate> mtmcs = project.getMtmCandidates();
			for (int i=0;i<mtmcs.size();i++) {
				List<ManyToManyCandidate> vmtmcs = project.getMtmCandidatesValues().get(i);
				exportMtmCandidateExcelSheet(wb,"MtmCandidate"+(i+1),mtmcs.get(i), vmtmcs);
			}
			
			List<org.light.core.Module> modules = project.getModules();
			for (org.light.core.Module m: modules) {
				if (m instanceof ShiroAuthModule) {
					exportShiroAuthModuleExcelSheet(wb,"Module1",(ShiroAuthModule)m);
				}
			}
			List<LayoutComb> layouts = project.getLayoutCombs();
			int i = 1;
			for (LayoutComb lc:layouts) {
				if (lc instanceof ParentChildGridLayout) {
					exportParentChildLayoutExcelSheet(wb,"Layout"+i,(ParentChildGridLayout)lc);
				}
				if (lc instanceof TreeGridLayout) {
					exportTreeGridLayoutExcelSheet(wb,"Layout"+i,(TreeGridLayout)lc);
				}
				if (lc instanceof TreeParentChildLayout) {
					exportTreeParentChildLayoutExcelSheet(wb,"Layout"+i,(TreeParentChildLayout)lc);
				}
				i++;
			}
			
			List<ReportComb> reports = project.getReportCombs();
			int j = 1;
			for (ReportComb rc:reports) {
				if (rc instanceof EChartsReport) {
					exportEchartsReportExcelSheet(wb,"Report"+j,(EChartsReport)rc);
				}
				if (rc instanceof EChartsGridReport) {
					exportEchartsGridReportExcelSheet(wb,"Report"+j,(EChartsGridReport)rc);
				}
				if (rc instanceof EChartsCompareGridReport) {
					exportEchartsCompareGridReportExcelSheet(wb,"Report"+j,(EChartsCompareGridReport)rc);
				}
				j++;
			}
			wb.write(out);
		}
	}

	public static void exportDomainExcelSheet(HSSFWorkbook wb,String sheetName,Domain domain,List<List<Domain>> datadomains) throws Exception{
		HSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(1, 400*12);
		sheet.setColumnWidth(2, 500*12);
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = wb.getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		HSSFFont headerFont = wb.createFont();
		//颜色
		headerFont.setColor(Font.COLOR_NORMAL);
		//字体
		headerFont.setFontName("Microsoft YaHei");
		//设置字体大小
		headerFont.setFontHeightInPoints((short) 11);
		headerFont.setBold(true);
		
		HSSFFont valueFont = wb.createFont();
		//颜色
		valueFont.setColor(Font.COLOR_NORMAL);
		//字体
		valueFont.setFontName("Microsoft YaHei");
		//设置字体大小
		valueFont.setFontHeightInPoints((short) 11);
		
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(valueFont);
		
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//bdIndex 边框颜色下标值
		cellStyle.setBottomBorderColor(bdIndex);
		cellStyle.setLeftBorderColor(bdIndex);
		cellStyle.setRightBorderColor(bdIndex);
		cellStyle.setTopBorderColor(bdIndex);
		
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFCellStyle cellHeaderStyle = wb.createCellStyle();
		cellHeaderStyle.cloneStyleFrom(cellStyle);
		cellHeaderStyle.setFont(headerFont);

		cellHeaderStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
		cellHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		List<Pair> pairs = new ArrayList<Pair>();
		
		Pair domainName = new Pair("domain",domain.getStandardName());
		Pair plural = new Pair("plural",domain.getPlural());		
		Pair tableprefix = new Pair("tableprefix",domain.getTablePrefix());
		Pair domainlabel = new Pair("domainlabel",domain.getLabel());
		Pair verbdenies = new Pair("verbdenies",domain.getVerbDeniesStr());

		pairs.add(domainName);
		pairs.add(plural);
		pairs.add(tableprefix);
		pairs.add(domainlabel);
		pairs.add(verbdenies);
		
		writePairs(sheet,bgIndex,bdIndex,1,cellHeaderStyle,cellStyle,pairs);

		//创建表格之后设置行高与列宽
		for(int i = 1; i < pairs.size()+1; i++) {
			row = sheet.getRow(i);
			if (row!=null) row.setHeightInPoints(24);
		}

		CellRangeAddress region = CellRangeAddress.valueOf("B2:C6");
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region,sheet);		
		
		List<String> headers = new ArrayList<String>();
		List<Field> fields = new ArrayList<Field>();
		fields.add(domain.getDomainId());
		fields.add(domain.getDomainName());
		fields.add(domain.getActive());
		fields.addAll(domain.getPlainFields());
		headers.add("元字段类型");
		if (domain.getDomainId()!=null) headers.add("id");
		if (domain.getDomainName()!=null) headers.add("domainname");
		if (domain.getActive()!=null) headers.add("activefield");
		for (Field f:domain.getPlainFields()) {
			if (f instanceof Dropdown) headers.add("dropdown");
			else headers.add("field");
		}
		for (ManyToMany mtm:domain.getManyToManies()) {
			headers.add("manytomanyslave");
		}
		 	
		List<List<String>> contents = new ArrayList<List<String>>();
		List<String> fieldNames = new ArrayList<String>();
		fieldNames.add("字段");
		if (domain.getDomainId()!=null) fieldNames.add(domain.getDomainId().getFieldName());
		if (domain.getDomainName()!=null) fieldNames.add(domain.getDomainName().getFieldName());
		if (domain.getActive()!=null) fieldNames.add(domain.getActive().getFieldName());
		for (Field f:domain.getPlainFields()) {
			fieldNames.add(f.getFieldName());
		}
		for (ManyToMany mtm:domain.getManyToManies()) {
			fieldNames.add(mtm.getStandardName());
		}
		contents.add(fieldNames);
		
		List<String> fieldTypes = new ArrayList<String>();
		fieldTypes.add("字段类型");
		if (domain.getDomainId()!=null) fieldTypes.add(domain.getDomainId().getFieldType());
		if (domain.getDomainName()!=null) fieldTypes.add(domain.getDomainName().getFieldType());
		if (domain.getActive()!=null) fieldTypes.add(domain.getActive().getFieldType());
		for (Field f:domain.getPlainFields()) {
			if (f instanceof Dropdown && ((Dropdown)f).getTarget()==null) fieldTypes.add(f.getFieldType());
			else if (f instanceof Dropdown) fieldTypes.add(((Dropdown)f).getTarget().getStandardName());
			else fieldTypes.add(f.getFieldType());
		}
		for (ManyToMany mtm:domain.getManyToManies()) {
			fieldTypes.add(mtm.getManyToManySalveName());
		}
		contents.add(fieldTypes);
		
		List<String> fieldLens = new ArrayList<String>();
		fieldLens.add("长度");
		if (domain.getDomainId()!=null) fieldLens.add(domain.getDomainId().getLengthStr());
		if (domain.getDomainName()!=null) fieldLens.add(domain.getDomainName().getLengthStr());
		if (domain.getActive()!=null) fieldLens.add(domain.getActive().getLengthStr());
		for (Field f:domain.getPlainFields()) {
			fieldLens.add(f.getLengthStr());
		}
		for (ManyToMany mtm:domain.getManyToManies()) {
			fieldLens.add("");
		}
		contents.add(fieldLens);
		
		List<String> fieldfixedNames = new ArrayList<String>();
		fieldfixedNames.add("固定名字");
		if (domain.getDomainId()!=null) {
			if (!domain.getDomainId().getFixedName().equals(domain.getDomainId().getFieldName())) {
				fieldfixedNames.add(domain.getDomainId().getFixedName());
			}else {
				fieldfixedNames.add("");
			}			
		}
		if (domain.getDomainName()!=null) {
			if(!domain.getDomainName().getFixedName().equals(domain.getDomainName().getFieldName())) {
				fieldfixedNames.add(domain.getDomainName().getFixedName());
			}else {
				fieldfixedNames.add("");
			}
		}
		if (domain.getActive()!=null) {
			if(!domain.getActive().getFixedName().equals(domain.getActive().getFieldName())) {
				fieldfixedNames.add(domain.getActive().getFixedName());
			}else {
				fieldfixedNames.add("");
			}			
		}
		for (Field f:domain.getPlainFields()) {
			if (!f.getFixedName().equals(f.getFieldName())){
				fieldfixedNames.add(f.getFixedName());
			}else {
				fieldfixedNames.add("");
			}
		}
		for (ManyToMany mtm:domain.getManyToManies()) {
			fieldfixedNames.add("");
		}
		if (domain.containsFixedNames()) contents.add(fieldfixedNames);

		List<String> fieldLabels = new ArrayList<String>();
		fieldLabels.add("字段标签");
		if (domain.getDomainId()!=null) fieldLabels.add(domain.getDomainId().getLabel());
		if (domain.getDomainName()!=null) fieldLabels.add(domain.getDomainName().getLabel());
		if (domain.getActive()!=null) fieldLabels.add(domain.getActive().getLabel());
		for (Field f:domain.getPlainFields()) {
			fieldLabels.add(f.getLabel());
		}
		for (ManyToMany mtm:domain.getManyToManies()) {
			fieldLabels.add(mtm.getSlaveAliasLabel());
		}
		contents.add(fieldLabels);		
		
		List<Domain> mydatadomains = DomainUtil.filterDataDomainList(datadomains, domain.getStandardName());
		
		for (int i=0;i<mydatadomains.size();i++) {
			List<String> datas = new ArrayList<String>();
			if (i==0) datas.add("数据");
			else datas.add("");
			Domain d = mydatadomains.get(i);
			if (domain.getDomainId()!=null) datas.add(d.getDomainId().getFieldValue());
			if (domain.getDomainName()!=null) datas.add(d.getDomainName().getFieldValue());
			if (domain.getActive()!=null) datas.add(d.getActive().getFieldValue());
			for (Field f:d.getPlainFields()) {
				if (f instanceof Dropdown && (StringUtil.isBlank(f.getFieldValue()) || f.getFieldValue().equals("0")||StringUtil.isNegativeInteger(f.getFieldValue()))){
					datas.add("");
				} else {
					datas.add(f.getFieldValue());
				}
			}
			for (ManyToMany mtm:d.getManyToManies()) {
				datas.add(mtm.getValues());
			}
			contents.add(datas);
		}		
		exportDomainBodyWithData(sheet,cellHeaderStyle, cellStyle,headers,contents);
	}
	
	public static void exportProjectExcelSheet(HSSFWorkbook wb, String sheetName,List<Pair> pairs) throws Exception{
		HSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(1, 500*12);
		sheet.setColumnWidth(2, 700*12);
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = wb.getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		HSSFFont headerFont = wb.createFont();
		//颜色
		headerFont.setColor(Font.COLOR_NORMAL);
		//字体
		headerFont.setFontName("Microsoft YaHei");
		//设置字体大小
		headerFont.setFontHeightInPoints((short) 11);
		headerFont.setBold(true);
		
		HSSFFont valueFont = wb.createFont();
		//颜色
		valueFont.setColor(Font.COLOR_NORMAL);
		//字体
		valueFont.setFontName("Microsoft YaHei");
		//设置字体大小
		valueFont.setFontHeightInPoints((short) 11);
		
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(valueFont);
		
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//bdIndex 边框颜色下标值
		cellStyle.setBottomBorderColor(bdIndex);
		cellStyle.setLeftBorderColor(bdIndex);
		cellStyle.setRightBorderColor(bdIndex);
		cellStyle.setTopBorderColor(bdIndex);
		
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFCellStyle cellHeaderStyle = wb.createCellStyle();
		cellHeaderStyle.cloneStyleFrom(cellStyle);
		cellHeaderStyle.setFont(headerFont);
		
		cellHeaderStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
		cellHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		writePairs(sheet,bgIndex,bdIndex,1,cellHeaderStyle,cellStyle,pairs);

		//创建表格之后设置行高与列宽
		for(int i = 1; i < pairs.size()+1; i++) {
			row = sheet.getRow(i);
			if (row!=null) row.setHeightInPoints(24);
		}

		CellRangeAddress region = CellRangeAddress.valueOf("B2:C28");
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region,sheet);
	}
	
	public static void exportDomainBodyWithData(HSSFSheet sheet,HSSFCellStyle cellHeaderStyle,HSSFCellStyle cellStyle,List<String> headers, List<List<String>> contents) throws Exception{
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = sheet.getWorkbook().getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		writeRow(sheet,bgIndex,bdIndex,6,cellHeaderStyle,headers);
		
		int rowCount=16;
		if (contents!=null&&contents.size()>16) rowCount = contents.size();

		if (contents!=null) {
			for(int i = 0; i < contents.size(); i++) {
				writeRow(sheet,bgIndex,bdIndex,i+7,cellStyle,contents.get(i));
			}
		}
		
		if (contents.size()< 16) {
			for(int i = 0; i < rowCount-contents.size(); i++) {
				if (i==0 &&contents.size()==4)writeEmptyRowWithHeadLabel(sheet,bgIndex,bdIndex,7+contents.size()+i,cellStyle,contents.get(0).size(),"数据");
				else writeEmptyRow(sheet,bgIndex,bdIndex,7+contents.size()+i,cellStyle,contents.get(0).size());
			}
		}
		
		//创建表格之后设置行高与列宽
		if (contents !=null) {
			int datasheethigh = contents.size() > 16?contents.size()+7:23;
			for(int i = 1; i < datasheethigh; i++) {
				row = sheet.getRow(i);
				row.setHeightInPoints(30);
			}
		}
		for(int j = 1; j < headers.size()+1; j++) {
			sheet.setColumnWidth(j, MSExcelUtil.pixel2WidthUnits(120));
		}
		
		CellRangeAddress region2 = new CellRangeAddress(6,(rowCount+6),1,headers.size());		
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region2,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region2,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region2,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region2,sheet);
	}
	
	public static void writeRow(HSSFSheet sheet,short bgIndex,short bdIndex,int rowIndex,HSSFCellStyle cellStyle,List<String> data) {
		HSSFRow row = sheet.createRow(rowIndex);//创建表格行
		for(int j = 0; j < data.size(); j++) {
			Cell cell = row.createCell(j+1);//根据表格行创建单元格
			cell.setCellStyle(cellStyle);
			cell.setCellValue(String.valueOf(StringUtil.nullTrim(data.get(j))));
		}
	}
	
	public static void writeEmptyRow(HSSFSheet sheet,short bgIndex,short bdIndex,int rowIndex,HSSFCellStyle cellStyle,int cellCount) {
		HSSFRow row = sheet.createRow(rowIndex);//创建表格行
		for(int j = 0; j < cellCount; j++) {
			Cell cell = row.createCell(j+1);//根据表格行创建单元格
			cell.setCellStyle(cellStyle);
			cell.setCellValue("");
		}
	}
	
	public static void writeEmptyRowWithHeadLabel(HSSFSheet sheet,short bgIndex,short bdIndex,int rowIndex,HSSFCellStyle cellStyle,int cellCount,String headerLabel) {
		HSSFRow row = sheet.createRow(rowIndex);//创建表格行
		for(int j = 0; j < cellCount; j++) {
			Cell cell = row.createCell(j+1);//根据表格行创建单元格
			cell.setCellStyle(cellStyle);
			if (j==0) cell.setCellValue(headerLabel);
			else cell.setCellValue("");
		}
	}
	
	public static void writePairs(HSSFSheet sheet,short bgIndex,short bdIndex,int rowIndex,HSSFCellStyle keyCellStyle,HSSFCellStyle valueCellStyle,List<Pair> pairs) {
		for(int j = 0; j < pairs.size(); j++) {
			HSSFRow row = sheet.createRow(rowIndex+j);//创建表格行
			Cell cell = row.createCell(1);//根据表格行创建单元格
			cell.setCellStyle(keyCellStyle);
			cell.setCellValue(String.valueOf(StringUtil.nullTrim(pairs.get(j).getKey())));
			
			Cell vcell = row.createCell(2);//根据表格行创建单元格
			vcell.setCellStyle(valueCellStyle);
			vcell.setCellValue(String.valueOf(StringUtil.nullTrim(pairs.get(j).getValue())));
		}
	}
	
	public static void exportShiroAuthModuleExcelSheet(HSSFWorkbook wb,String sheetName,ShiroAuthModule sam) throws Exception{
		HSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(1, 500*12);
		sheet.setColumnWidth(2, 700*12);
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = wb.getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		HSSFFont headerFont = wb.createFont();
		//颜色
		headerFont.setColor(Font.COLOR_NORMAL);
		//字体
		headerFont.setFontName("Microsoft YaHei");
		//设置字体大小
		headerFont.setFontHeightInPoints((short) 11);
		headerFont.setBold(true);
		
		HSSFFont valueFont = wb.createFont();
		//颜色
		valueFont.setColor(Font.COLOR_NORMAL);
		//字体
		valueFont.setFontName("Microsoft YaHei");
		//设置字体大小
		valueFont.setFontHeightInPoints((short) 11);
		
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(valueFont);
		
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//bdIndex 边框颜色下标值
		cellStyle.setBottomBorderColor(bdIndex);
		cellStyle.setLeftBorderColor(bdIndex);
		cellStyle.setRightBorderColor(bdIndex);
		cellStyle.setTopBorderColor(bdIndex);
		
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFCellStyle cellHeaderStyle = wb.createCellStyle();
		cellHeaderStyle.cloneStyleFrom(cellStyle);
		cellHeaderStyle.setFont(headerFont);
		
		cellHeaderStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
		cellHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		List<Pair> pairs = new ArrayList<Pair>();
		Pair moduleName = new Pair("module","ShiroAuth");
		Pair userDomainName = new Pair("userdomain",sam.getUserDomain().getStandardName());
		Pair roleDomainName = new Pair("roledomain",sam.getRoleDomain().getStandardName());
		Pair privilegeDomainName = new Pair("privilegedomain",sam.getPrivilegeDomain().getStandardName());
		pairs.add(moduleName);
		pairs.add(userDomainName);
		pairs.add(roleDomainName);
		pairs.add(privilegeDomainName);
		
		writePairs(sheet,bgIndex,bdIndex,1,cellHeaderStyle,cellStyle,pairs);

		//创建表格之后设置行高与列宽
		for(int i = 1; i < pairs.size()+1; i++) {
			row = sheet.getRow(i);
			if (row!=null) row.setHeightInPoints(24);
		}

		CellRangeAddress region = CellRangeAddress.valueOf("B2:C5");
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region,sheet);
	}
	
	public static void exportParentChildLayoutExcelSheet(HSSFWorkbook wb,String sheetName,ParentChildGridLayout pcgl) throws Exception{
		HSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(1, 500*12);
		sheet.setColumnWidth(2, 700*12);
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = wb.getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		HSSFFont headerFont = wb.createFont();
		//颜色
		headerFont.setColor(Font.COLOR_NORMAL);
		//字体
		headerFont.setFontName("Microsoft YaHei");
		//设置字体大小
		headerFont.setFontHeightInPoints((short) 11);
		headerFont.setBold(true);
		
		HSSFFont valueFont = wb.createFont();
		//颜色
		valueFont.setColor(Font.COLOR_NORMAL);
		//字体
		valueFont.setFontName("Microsoft YaHei");
		//设置字体大小
		valueFont.setFontHeightInPoints((short) 11);
		
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(valueFont);
		
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//bdIndex 边框颜色下标值
		cellStyle.setBottomBorderColor(bdIndex);
		cellStyle.setLeftBorderColor(bdIndex);
		cellStyle.setRightBorderColor(bdIndex);
		cellStyle.setTopBorderColor(bdIndex);
		
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFCellStyle cellHeaderStyle = wb.createCellStyle();
		cellHeaderStyle.cloneStyleFrom(cellStyle);
		cellHeaderStyle.setFont(headerFont);
		
		cellHeaderStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
		cellHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		List<Pair> pairs = new ArrayList<Pair>();
		Pair layoutName = new Pair("layout","ParentChild");
		Pair parentDomainName = new Pair("parentdomain",pcgl.getParentDomain().getStandardName());
		Pair childDomainName = new Pair("childdomain",pcgl.getChildDomain().getStandardName());
		Pair parentidfield = new Pair("parentidfield",pcgl.getParentId());
		pairs.add(layoutName);
		pairs.add(parentDomainName);
		pairs.add(childDomainName);
		pairs.add(parentidfield);
		
		writePairs(sheet,bgIndex,bdIndex,1,cellHeaderStyle,cellStyle,pairs);

		//创建表格之后设置行高与列宽
		for(int i = 1; i < pairs.size()+1; i++) {
			row = sheet.getRow(i);
			if (row!=null) row.setHeightInPoints(24);
		}

		CellRangeAddress region = CellRangeAddress.valueOf("B2:C5");
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region,sheet);
	}
	
	public static void exportTreeGridLayoutExcelSheet(HSSFWorkbook wb,String sheetName,TreeGridLayout tgl) throws Exception{
		HSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(1, 500*12);
		sheet.setColumnWidth(2, 700*12);
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = wb.getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		HSSFFont headerFont = wb.createFont();
		//颜色
		headerFont.setColor(Font.COLOR_NORMAL);
		//字体
		headerFont.setFontName("Microsoft YaHei");
		//设置字体大小
		headerFont.setFontHeightInPoints((short) 11);
		headerFont.setBold(true);
		
		HSSFFont valueFont = wb.createFont();
		//颜色
		valueFont.setColor(Font.COLOR_NORMAL);
		//字体
		valueFont.setFontName("Microsoft YaHei");
		//设置字体大小
		valueFont.setFontHeightInPoints((short) 11);
		
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(valueFont);
		
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//bdIndex 边框颜色下标值
		cellStyle.setBottomBorderColor(bdIndex);
		cellStyle.setLeftBorderColor(bdIndex);
		cellStyle.setRightBorderColor(bdIndex);
		cellStyle.setTopBorderColor(bdIndex);
		
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFCellStyle cellHeaderStyle = wb.createCellStyle();
		cellHeaderStyle.cloneStyleFrom(cellStyle);
		cellHeaderStyle.setFont(headerFont);
		
		cellHeaderStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
		cellHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		List<Pair> pairs = new ArrayList<Pair>();
		Pair layoutName = new Pair("layout","TreeGrid");
		Pair treeDomainName = new Pair("treedomain",tgl.getParentTreeDomain().getStandardName());
		Pair childDomainName = new Pair("childdomain",tgl.getChildDomain().getStandardName());
		Pair parentidfield = new Pair("parentidfield",tgl.getParentId());
		Pair innertreeparentidfield = new Pair("innertreeparentidfield",tgl.getInnerTreeParentId());
		pairs.add(layoutName);
		pairs.add(treeDomainName);
		pairs.add(childDomainName);
		pairs.add(innertreeparentidfield);
		pairs.add(parentidfield);		
		
		writePairs(sheet,bgIndex,bdIndex,1,cellHeaderStyle,cellStyle,pairs);

		//创建表格之后设置行高与列宽
		for(int i = 1; i < pairs.size()+1; i++) {
			row = sheet.getRow(i);
			if (row!=null) row.setHeightInPoints(24);
		}

		CellRangeAddress region = CellRangeAddress.valueOf("B2:C6");
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region,sheet);
	}
	
	public static void exportTreeParentChildLayoutExcelSheet(HSSFWorkbook wb,String sheetName,TreeParentChildLayout tpcl) throws Exception{
		HSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(1, 500*12);
		sheet.setColumnWidth(2, 700*12);
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = wb.getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		HSSFFont headerFont = wb.createFont();
		//颜色
		headerFont.setColor(Font.COLOR_NORMAL);
		//字体
		headerFont.setFontName("Microsoft YaHei");
		//设置字体大小
		headerFont.setFontHeightInPoints((short) 11);
		headerFont.setBold(true);
		
		HSSFFont valueFont = wb.createFont();
		//颜色
		valueFont.setColor(Font.COLOR_NORMAL);
		//字体
		valueFont.setFontName("Microsoft YaHei");
		//设置字体大小
		valueFont.setFontHeightInPoints((short) 11);
		
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(valueFont);
		
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//bdIndex 边框颜色下标值
		cellStyle.setBottomBorderColor(bdIndex);
		cellStyle.setLeftBorderColor(bdIndex);
		cellStyle.setRightBorderColor(bdIndex);
		cellStyle.setTopBorderColor(bdIndex);
		
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFCellStyle cellHeaderStyle = wb.createCellStyle();
		cellHeaderStyle.cloneStyleFrom(cellStyle);
		cellHeaderStyle.setFont(headerFont);
		
		cellHeaderStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
		cellHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		List<Pair> pairs = new ArrayList<Pair>();
		Pair layoutName = new Pair("layout","TreeParentChild");
		Pair treeDomainName = new Pair("treedomain",tpcl.getTreeDomain().getStandardName());
		Pair parentDomainName = new Pair("parentdomain",tpcl.getParentDomain().getStandardName());
		Pair childDomainName = new Pair("childdomain",tpcl.getChildDomain().getStandardName());
		Pair treeparentidfield = new Pair("treeparentidfield",tpcl.getTreeParentId());
		Pair parentidfield = new Pair("parentidfield",tpcl.getParentId());
		Pair innertreeparentidfield = new Pair("innertreeparentidfield",tpcl.getInnerTreeParentId());
		pairs.add(layoutName);
		pairs.add(treeDomainName);
		pairs.add(parentDomainName);
		pairs.add(childDomainName);
		pairs.add(innertreeparentidfield);
		pairs.add(treeparentidfield);
		pairs.add(parentidfield);
		
		writePairs(sheet,bgIndex,bdIndex,1,cellHeaderStyle,cellStyle,pairs);

		//创建表格之后设置行高与列宽
		for(int i = 1; i < pairs.size()+1; i++) {
			row = sheet.getRow(i);
			if (row!=null) row.setHeightInPoints(24);
		}

		CellRangeAddress region = CellRangeAddress.valueOf("B2:C8");
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region,sheet);
	}
	
	public static void exportEchartsReportExcelSheet(HSSFWorkbook wb,String sheetName,EChartsReport report) throws Exception{
		HSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(1, 500*12);
		sheet.setColumnWidth(2, 700*12);
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = wb.getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		HSSFFont headerFont = wb.createFont();
		//颜色
		headerFont.setColor(Font.COLOR_NORMAL);
		//字体
		headerFont.setFontName("Microsoft YaHei");
		//设置字体大小
		headerFont.setFontHeightInPoints((short) 11);
		headerFont.setBold(true);
		
		HSSFFont valueFont = wb.createFont();
		//颜色
		valueFont.setColor(Font.COLOR_NORMAL);
		//字体
		valueFont.setFontName("Microsoft YaHei");
		//设置字体大小
		valueFont.setFontHeightInPoints((short) 11);
		
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(valueFont);
		
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//bdIndex 边框颜色下标值
		cellStyle.setBottomBorderColor(bdIndex);
		cellStyle.setLeftBorderColor(bdIndex);
		cellStyle.setRightBorderColor(bdIndex);
		cellStyle.setTopBorderColor(bdIndex);
		
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFCellStyle cellHeaderStyle = wb.createCellStyle();
		cellHeaderStyle.cloneStyleFrom(cellStyle);
		cellHeaderStyle.setFont(headerFont);
		
		cellHeaderStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
		cellHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		List<Pair> pairs = new ArrayList<Pair>();
		Pair reportName = new Pair("report","EchartsReport");
		Pair rportDomainName = new Pair("reportdomain",report.getReportDomain().getStandardName());
		Pair xaxisfields = new Pair("xaxisfields",report.getxAxisFieldsNames());
		Pair yname = new Pair("yname",report.getyName().getFieldName());
		pairs.add(reportName);
		pairs.add(rportDomainName);
		pairs.add(xaxisfields);
		pairs.add(yname);
		
		writePairs(sheet,bgIndex,bdIndex,1,cellHeaderStyle,cellStyle,pairs);

		//创建表格之后设置行高与列宽
		for(int i = 1; i < pairs.size()+1; i++) {
			row = sheet.getRow(i);
			if (row!=null) row.setHeightInPoints(24);
		}

		CellRangeAddress region = CellRangeAddress.valueOf("B2:C5");
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region,sheet);
	}
	
	public static void exportEchartsGridReportExcelSheet(HSSFWorkbook wb,String sheetName,EChartsGridReport report) throws Exception{
		HSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(1, 500*12);
		sheet.setColumnWidth(2, 700*12);
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = wb.getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		HSSFFont headerFont = wb.createFont();
		//颜色
		headerFont.setColor(Font.COLOR_NORMAL);
		//字体
		headerFont.setFontName("Microsoft YaHei");
		//设置字体大小
		headerFont.setFontHeightInPoints((short) 11);
		headerFont.setBold(true);
		
		HSSFFont valueFont = wb.createFont();
		//颜色
		valueFont.setColor(Font.COLOR_NORMAL);
		//字体
		valueFont.setFontName("Microsoft YaHei");
		//设置字体大小
		valueFont.setFontHeightInPoints((short) 11);
		
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(valueFont);
		
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//bdIndex 边框颜色下标值
		cellStyle.setBottomBorderColor(bdIndex);
		cellStyle.setLeftBorderColor(bdIndex);
		cellStyle.setRightBorderColor(bdIndex);
		cellStyle.setTopBorderColor(bdIndex);
		
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFCellStyle cellHeaderStyle = wb.createCellStyle();
		cellHeaderStyle.cloneStyleFrom(cellStyle);
		cellHeaderStyle.setFont(headerFont);
		
		cellHeaderStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
		cellHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		List<Pair> pairs = new ArrayList<Pair>();
		Pair reportName = new Pair("report","EchartsGridReport");
		Pair rportDomainName = new Pair("reportdomain",report.getReportDomain().getStandardName());
		Pair xaxisfields = new Pair("xaxisfields",report.getxAxisFieldsNames());
		Pair yname = new Pair("yname",report.getyName().getFieldName());
		pairs.add(reportName);
		pairs.add(rportDomainName);
		pairs.add(xaxisfields);
		pairs.add(yname);
		
		writePairs(sheet,bgIndex,bdIndex,1,cellHeaderStyle,cellStyle,pairs);

		//创建表格之后设置行高与列宽
		for(int i = 1; i < pairs.size()+1; i++) {
			row = sheet.getRow(i);
			if (row!=null) row.setHeightInPoints(24);
		}

		CellRangeAddress region = CellRangeAddress.valueOf("B2:C5");
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region,sheet);
	}
	
	public static void exportEchartsCompareGridReportExcelSheet(HSSFWorkbook wb,String sheetName,EChartsCompareGridReport report) throws Exception{
		HSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(1, 500*12);
		sheet.setColumnWidth(2, 700*12);
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = wb.getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		HSSFFont headerFont = wb.createFont();
		//颜色
		headerFont.setColor(Font.COLOR_NORMAL);
		//字体
		headerFont.setFontName("Microsoft YaHei");
		//设置字体大小
		headerFont.setFontHeightInPoints((short) 11);
		headerFont.setBold(true);
		
		HSSFFont valueFont = wb.createFont();
		//颜色
		valueFont.setColor(Font.COLOR_NORMAL);
		//字体
		valueFont.setFontName("Microsoft YaHei");
		//设置字体大小
		valueFont.setFontHeightInPoints((short) 11);
		
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(valueFont);
		
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//bdIndex 边框颜色下标值
		cellStyle.setBottomBorderColor(bdIndex);
		cellStyle.setLeftBorderColor(bdIndex);
		cellStyle.setRightBorderColor(bdIndex);
		cellStyle.setTopBorderColor(bdIndex);
		
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFCellStyle cellHeaderStyle = wb.createCellStyle();
		cellHeaderStyle.cloneStyleFrom(cellStyle);
		cellHeaderStyle.setFont(headerFont);
		
		cellHeaderStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
		cellHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		List<Pair> pairs = new ArrayList<Pair>();
		Pair reportName = new Pair("report","EchartsCompareGridReport");
		Pair planDomainName = new Pair("plandomain",report.getPlanDomain().getStandardName());
		Pair planxaxisfields = new Pair("planxaxisfields",report.getPlanxAxisFieldsNames());
		Pair planyname = new Pair("planyname",report.getPlanyName().getFieldName());
		Pair actionDomainName = new Pair("actiondomain",report.getActionDomain().getStandardName());
		Pair actionxaxisfields = new Pair("actionxaxisfields",report.getActionxAxisFieldsNames());
		Pair actionyname = new Pair("actionyname",report.getActionyName().getFieldName());
		pairs.add(reportName);
		pairs.add(planDomainName);
		pairs.add(planxaxisfields);
		pairs.add(planyname);
		pairs.add(actionDomainName);
		pairs.add(actionxaxisfields);
		pairs.add(actionyname);
		
		writePairs(sheet,bgIndex,bdIndex,1,cellHeaderStyle,cellStyle,pairs);

		//创建表格之后设置行高与列宽
		for(int i = 1; i < pairs.size()+1; i++) {
			row = sheet.getRow(i);
			if (row!=null) row.setHeightInPoints(24);
		}

		CellRangeAddress region = CellRangeAddress.valueOf("B2:C8");
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region,sheet);
	}

	private static void exportMtmCandidateExcelSheet(HSSFWorkbook wb, String sheetName,
			ManyToManyCandidate mtmc, List<ManyToManyCandidate> vmtmcs) throws Exception{
		HSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(1, 400*12);
		sheet.setColumnWidth(2, 500*12);
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = wb.getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		HSSFFont headerFont = wb.createFont();
		//颜色
		headerFont.setColor(Font.COLOR_NORMAL);
		//字体
		headerFont.setFontName("Microsoft YaHei");
		//设置字体大小
		headerFont.setFontHeightInPoints((short) 11);
		headerFont.setBold(true);
		
		HSSFFont valueFont = wb.createFont();
		//颜色
		valueFont.setColor(Font.COLOR_NORMAL);
		//字体
		valueFont.setFontName("Microsoft YaHei");
		//设置字体大小
		valueFont.setFontHeightInPoints((short) 11);
		
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(valueFont);
		
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//bdIndex 边框颜色下标值
		cellStyle.setBottomBorderColor(bdIndex);
		cellStyle.setLeftBorderColor(bdIndex);
		cellStyle.setRightBorderColor(bdIndex);
		cellStyle.setTopBorderColor(bdIndex);
		
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFCellStyle cellHeaderStyle = wb.createCellStyle();
		cellHeaderStyle.cloneStyleFrom(cellStyle);
		cellHeaderStyle.setFont(headerFont);

		cellHeaderStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
		cellHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		List<Pair> pairs = new ArrayList<Pair>();
		
		Pair masterDomainName = new Pair("masterDomain",StringUtil.nullTrim(mtmc.getMasterName()));
		
		String masterIdNameStr = "";
		if (mtmc.getMasterId()!=null) masterIdNameStr = StringUtil.nullTrim(mtmc.getMasterId().getLowerFirstFieldName());
		Pair masterIdName = new Pair("masterIdName",masterIdNameStr);
		
		String slaveDomainNameStr = "";
		if (mtmc.getSlave()!=null) slaveDomainNameStr = StringUtil.nullTrim(mtmc.getSlave().getCapFirstDomainName());
		Pair slaveDomainName = new Pair("slaveDomain",slaveDomainNameStr);
		
		Pair slaveAlias = new Pair("slaveAlias",StringUtil.nullTrim(mtmc.getSlaveAlias()));
		
		String slaveIdNameStr = "";
		if (mtmc.getSlaveId()!=null) slaveIdNameStr = StringUtil.nullTrim(mtmc.getSlaveId().getLowerFirstFieldName());
		Pair slaveIdName = new Pair("slaveIdName",slaveIdNameStr);
		Pair tableprefix = new Pair("tableprefix",StringUtil.nullTrim(mtmc.getTablePrefix()));
		Pair label = new Pair("label",StringUtil.nullTrim(mtmc.getLabel()));

		pairs.add(masterDomainName);
		pairs.add(masterIdName);
		pairs.add(slaveDomainName);
		pairs.add(slaveAlias);
		pairs.add(slaveIdName);
		pairs.add(tableprefix);
		pairs.add(label);
		
		writePairs(sheet,bgIndex,bdIndex,1,cellHeaderStyle,cellStyle,pairs);

		//创建表格之后设置行高与列宽
		for(int i = 1; i < pairs.size()+1; i++) {
			row = sheet.getRow(i);
			if (row!=null) row.setHeightInPoints(24);
		}

		CellRangeAddress region = CellRangeAddress.valueOf("B2:C8");
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region,sheet);		
		
		List<String> headers = new ArrayList<String>();
		List<Field> fields = new ArrayList<Field>();
		fields.add(mtmc.getMasterId());
		fields.add(mtmc.getSlaveId());
		headers.add("元字段类型");
		headers.add("masterId");
		headers.add("slaveIds");
		 	
		List<List<String>> contents = new ArrayList<List<String>>();
		
		if (vmtmcs ==null || vmtmcs.size()==0) {
			List<String> datas = new ArrayList<String>();
			datas.add("数据");
			datas.add("");
			datas.add("");
			contents.add(datas);
		} else if (vmtmcs != null && vmtmcs.size()>0) {
			for (int i=0; i<vmtmcs.size(); i++) {
				List<String> datas = new ArrayList<String>();
				if (i == 0) datas.add("数据");
				else datas.add("");
				datas.add(vmtmcs.get(i).getMasterIdValue());
				datas.add(vmtmcs.get(i).getSlaveIdValues());
				contents.add(datas);
			}
		}
		exportMtmCandidateBodyWithData(sheet,cellHeaderStyle, cellStyle,headers,contents);		
	}

	public static void exportMtmCandidateBodyWithData(HSSFSheet sheet,HSSFCellStyle cellHeaderStyle,HSSFCellStyle cellStyle,List<String> headers, List<List<String>> contents) throws Exception{
		HSSFRow row;
		HSSFCell cell;
		
		short colorIndex = 10;
		HSSFPalette palette = sheet.getWorkbook().getCustomPalette();
		Color rgb = Color.YELLOW;
		short bgIndex = colorIndex ++;
		palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		short bdIndex = colorIndex ++;
		rgb = Color.BLACK;
		palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
		
		writeRow(sheet,bgIndex,bdIndex,8,cellHeaderStyle,headers);
		
		int rowCount=12;
		if (contents!=null&&contents.size()>12) rowCount = contents.size();

		if (contents!=null) {
			for(int i = 0; i < contents.size(); i++) {
				writeRow(sheet,bgIndex,bdIndex,i+9,cellStyle,contents.get(i));
			}
		}
		
		if (contents.size()< 12) {
			for(int i = 0; i < rowCount-contents.size(); i++) {
				writeEmptyRow(sheet,bgIndex,bdIndex,9+contents.size()+i,cellStyle,contents.get(0).size());
			}
		}
		
		//创建表格之后设置行高与列宽
		if (contents !=null) {
			int datasheethigh = contents.size() > 12 ?contents.size()+9:21;
			for(int i = 1; i < datasheethigh; i++) {
				row = sheet.getRow(i);
				row.setHeightInPoints(30);
			}
		}
		for(int j = 1; j < headers.size()+1; j++) {
			sheet.setColumnWidth(j, MSExcelUtil.pixel2WidthUnits(120));
		}
		
		CellRangeAddress region2 = new CellRangeAddress(8,(rowCount+8),1,headers.size());		
		RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region2,sheet);
		RegionUtil.setBorderTop(BorderStyle.MEDIUM,region2,sheet);
		RegionUtil.setBorderLeft(BorderStyle.MEDIUM,region2,sheet);
		RegionUtil.setBorderRight(BorderStyle.MEDIUM,region2,sheet);
	}
}
